import logging
from datetime import datetime
from decimal import Decimal
from typing import Union, Optional, List

from fastapi import APIRouter, Depends, Path
from fastapi.params import Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

mrk_head2_router = APIRouter(prefix="/mrkHead2", tags=["耗材报损管理"], dependencies=[Depends(oauth2_scheme)])


class MrkSearch(PaginationBase):
    fsiteNo: str = Field(None, description="客户编号")
    ftakedateStart: str = Field(None, description="开始时间")
    ftakedateEnd: str = Field(None, description="结束时间")
    fbilltype: str = Field(None, description="报废类型(1:仓库报废/2:储物柜报废)")
    fmrkuser: str = Field(None, description="报废人员")
    fstockno: str = Field(None, description="仓库编号")
    fbillno: str = Field(None, description="单号")
    fbillstate: str = Field(None, description="单据状态(1:未报废/2:已报废/8:已作废)")


@mrk_head2_router.get("/list", summary="查询耗材报损信息")
def get_mrk_head2(mrk: MrkSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.m_tc_mrk_head where 1=1 "
    if mrk.fsiteNo is not None:
        sql = sql + " and fsite_no = :fsite_no "
        param["fsite_no"] = mrk.fsiteNo
    if mrk.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = mrk.fbillno
    if mrk.fmrkuser is not None:
        sql = sql + " and fmrkuser = :fmrkuser "
        param["fmrkuser"] = mrk.fmrkuser
    if mrk.fstockno is not None:
        sql = sql + " and fstockno = :fstockno"
        param["fstockno"] = mrk.fstockno
    if mrk.fbilltype is not None:
        sql = sql + " and fbilltype = :fbilltype "
        param["fbilltype"] = mrk.fbilltype
    if mrk.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = mrk.fbillstate
    if (mrk.ftakedateStart is not None) and (mrk.ftakedateEnd is not None):
        sql = sql + " and to_char(fmrkdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = mrk.ftakedateStart
        param["ftakedateEnd"] = mrk.ftakedateEnd

    count = db.execute(text(sql), param).scalar()

    # 查询主表
    param = {}
    sql = "select fbillno,fsite_no,fmrkuser,fmrkdate,fstockno,fitemcount,fpiececount,ftotalamt,"
    sql = sql + " fsrcbillno,fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate,fbilltype"
    sql = sql + " from lims.m_tc_mrk_head where 1=1"
    if mrk.fsiteNo != "":
        sql = sql + " and fsite_no = :fsite_no "
        param["fsite_no"] = mrk.fsiteNo
    if mrk.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = mrk.fbillno
    if mrk.fmrkuser != "":
        sql = sql + " and fmrkuser = :fmrkuser "
        param["fmrkuser"] = mrk.fmrkuser
    if mrk.fstockno != "":
        sql = sql + " and fstockno = :fstockno"
        param["fstockno"] = mrk.fstockno
    if mrk.fbilltype != "":
        sql = sql + " and fbilltype = :fbilltype "
        param["fbilltype"] = mrk.fbilltype
    if mrk.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = mrk.fbillstate
    if (mrk.ftakedateStart != "") and (mrk.ftakedateEnd != ""):
        sql = sql + " and to_char(fmrkdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = mrk.ftakedateStart
        param["ftakedateEnd"] = mrk.ftakedateEnd
    if mrk.order == "desc":
        sql = sql + " order by  fmrkdate,fbillno desc "
    else:
        sql = sql + " order by  fmrkdate,fbillno "
    sql = sql + " limit :limit offset :offset"
    param["limit"] = mrk.limit
    param["offset"] = mrk.offset

    data = db.execute(text(sql), param).fetchall()

    # 查询明细
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fmaterialno,b.fbuyprice,b.fqty,b.ftotamt,b.fprocdate,b.fmemo"
    sql_3 = sql_3 + " from lims.m_tc_mrk_head a,lims.m_tc_mrk_detail b where a.fbillno=b.fbillno "
    if mrk.fsiteNo != "":
        sql_3 = sql_3 + " and a.fsite_no = :site_no "
        param["site_no"] = mrk.fsiteNo
    if mrk.fbillno != "":
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%') "
        param["fbillno"] = mrk.fbillno
    if mrk.fmrkuser != "":
        sql_3 = sql_3 + " and a.fmrkuser = :fmrkuser "
        param["fmrkuser"] = mrk.fmrkuser
    if mrk.fstockno != "":
        sql_3 = sql_3 + " and a.fstockno = :fstockno "
        param["fstockno"] = mrk.fstockno
    if mrk.fbilltype != "":
        sql_3 = sql_3 + " and a.fbilltype = :fbilltype "
        param["fbilltype"] = mrk.fbilltype
    if mrk.fbillstate != "":
        sql_3 = sql_3 + " and a.fbillstate = :fbillstate "
        param["fbillstate"] = mrk.fbillstate
    if (mrk.ftakedateStart != "") and (mrk.ftakedateEnd != ""):
        sql_3 = sql_3 + " and to_char(a.fmrkdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = mrk.ftakedateStart
        param["ftakedateEnd"] = mrk.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['fmrkuser'] = data[key][2]
        d1['fmrkdate'] = data[key][3]
        d1['fstockno'] = data[key][4]
        d1['fitemcount'] = data[key][5]
        d1['fpiececount'] = data[key][6]
        d1['ftotalamt'] = data[key][7]
        d1['fsrcbillno'] = data[key][8]
        d1['fexplanation'] = data[key][9]
        d1['fupddate'] = data[key][10]
        d1['foperator'] = data[key][11]
        d1['fcheckdate'] = data[key][12]
        d1['fcheckuser'] = data[key][13]
        d1['fbillstate'] = data[key][14]
        d1['fbilltype'] = data[key][15]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fmaterialno'] = data_detail[j1][2]
                d2['fbuyprice'] = data_detail[j1][3]
                d2['fqty'] = data_detail[j1][4]
                d2['ftotamt'] = data_detail[j1][5]
                d2['fprocdate'] = data_detail[j1][6]
                d2['fmemo'] = data_detail[j1][7]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": mrk.limit, "page": mrk.pageIndex,
                 "pages": (count + mrk.limit - 1) // mrk.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class DamageDetailCreate(BaseModel):
    """耗材报损明细项的数据模型"""
    fline: int = Field(default=-1, description="行号")
    fmaterialno: str = Field(default="", description="耗材编号")
    fbuyprice: Decimal = Field(default=Decimal("0"), description="购买价格")
    fqty: Decimal = Field(default=Decimal("0"), description="数量")
    fprocdate: Union[datetime, str] = Field(default="", description="处理日期")
    ftotamt: Decimal = Field(default=Decimal("0"), description="总金额")
    fmemo: str = Field(default="", description="备注")
    fmaterialname: Optional[str] = Field(default="", description="耗材名称")


class DamageHeadCreate(BaseModel):
    """耗材报损头信息的数据模型"""
    fbillno: str = Field(description="单据编号")
    fmrkdate: Union[datetime, str] = Field(description="报损日期")
    fmrkuser: str = Field(description="报损用户")
    fsiteNo: str = Field(description="站点编号")
    fstockno: str = Field(description="仓库编号")
    fexplanation: str = Field(default="", description="说明")
    details: List[DamageDetailCreate] = Field(description="报损明细列表")


@mrk_head2_router.post("/create", summary="新建耗材报损")
def create_mrk_head2(mrk: DamageHeadCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.decrypt_token(token)
        fbillno = mrk.fbillno
        with db.begin():
            fitemcount = 0
            fpiececount = 0
            ftotalamt = 0
            d1 = {}
            detail_list = []
            jsondata = mrk.details
            for data in jsondata:
                sql = "insert into lims.m_tc_mrk_detail(fbillno,fline,fmaterialno,fbuyprice,fqty,ftotamt,fprocdate,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fmaterialno , :fbuyprice , :fqty , :ftotamt , :fprocdate , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fmaterialno": data.fmaterialno,
                    "fbuyprice": data.fbuyprice,
                    "fqty": data.fqty,
                    "ftotamt": data.ftotamt,
                    "fprocdate": data.fprocdate,
                    "fmemo": data.fmemo
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + data.fqty
                ftotalamt = ftotalamt + data.ftotamt
                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data.fline
                d2['fmaterialno'] = data.fmaterialno
                d2['fmaterialname'] = data.fmaterialname
                d2['fbuyprice'] = float(data.fbuyprice)
                d2['fqty'] = float(data.fqty)
                d2['ftotamt'] = float(data.ftotamt)
                d2['fmemo'] = ""
                d2['fprocdate'] = data.fprocdate
                detail_list.append(d2)
            sql = "insert into lims.m_tc_mrk_head(fbillno,fsite_no,fmrkuser,fmrkdate,fstockno,fitemcount,fpiececount,ftotalamt,fexplanation,fupddate,foperator,fbillstate,fbilltype)"
            sql = sql + " values( :fbillno , :fsiteNo , :fmrkuser , :fmrkdate , :fstockno , :fitemcount , :fpiececount , :ftotalamt , :fexplanation ,now(), :fmrkuser ,1,1)"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": mrk.fsiteNo,
                "fmrkuser": mrk.fmrkuser,
                "fmrkdate": mrk.fmrkdate,
                "fstockno": mrk.fstockno,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "fexplanation": mrk.fexplanation
            })
            # 返回
            d1['fbillno'] = fbillno
            d1['fsiteno'] = mrk.fsiteNo
            d1['fmrkuser'] = mrk.fmrkuser
            d1['fmrkdate'] = mrk.fmrkdate
            d1['fstockno'] = mrk.fstockno
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['ftotalamt'] = float(ftotalamt)
            d1['fsrcbillno'] = ""
            d1['fexplanation'] = ""
            d1['fupddate'] = mrk.fmrkdate
            d1['foperator'] = mrk.fmrkuser
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fbillstate'] = 1
            d1['fbilltype'] = 1
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@mrk_head2_router.post("/update", summary="修改耗材报损信息")
def update_mrk_head2(mrk: DamageHeadCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.decrypt_token(token)
        fbillno = mrk.fbillno
        with db.begin():
            fitemcount = 0
            fpiececount = 0
            ftotalamt = 0
            # 删除详情
            sql = "delete from lims.m_tc_mrk_detail where fbillno= :fbillno "
            db.execute(text(sql), {"fbillno": fbillno})
            # 新增详情
            details = mrk.details
            for data in details:
                sql = "insert into lims.m_tc_mrk_detail(fbillno,fline,fmaterialno,fbuyprice,fqty,ftotamt,fprocdate,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fmaterialno , :fbuyprice , :fqty , :ftotamt , :fprocdate , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fmaterialno": data.fmaterialno,
                    "fbuyprice": data.fbuyprice,
                    "fqty": data.fqty,
                    "ftotamt": data.ftotamt,
                    "fprocdate": data.fprocdate,
                    "fmemo": data.fmemo
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + data.fqty
                ftotalamt = ftotalamt + data.ftotamt
            # 修改主表
            sql = "update lims.m_tc_mrk_head "
            sql = sql + " set fstockno= :fstockno ,fexplanation= :fexplanation "
            sql = sql + ",fitemcount= :fitemcount ,fpiececount= :fpiececount ,ftotalamt= :ftotalamt "
            sql = sql + ",fupddate=now(),foperator= :userid "
            sql = sql + " where fbillno= :fbillno "

            db.execute(text(sql), {
                "fstockno": mrk.fstockno,
                "fexplanation": mrk.fexplanation,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "userid": user_id,
                "fbillno": fbillno,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@mrk_head2_router.post("/delete/{fbillno}", summary="删除耗材报损记录")
def delete_mar_head2(fbillno: str = Path(description="单号"), db: Session = Depends(get_db)):
    try:
        user_id = EncryptionMiddleware.decrypt_token(fbillno)
        with db.begin():
            sql = "select count(1) from lims.m_tc_mrk_head where fbillno= :fbillno and fbillstate>1"
            data = db.execute(text(sql), {"fbillno": fbillno}).scalar()
            if data > 0:
                return BaseResponse.error(message="该订单不能删除")
            sql = "delete from lims.m_tc_mrk_detail where fbillno= :fbillno "
            db.execute(text(sql), {"fbillno": fbillno})
            sql = "delete from lims.m_tc_mrk_head where fbillno= :fbillno "
            db.execute(text(sql), {"fbillno": fbillno})
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
